package com.firesrc.util;

import java.io.IOException;
import java.io.StringReader;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import org.h2.store.fs.FileUtils;
import org.h2.tools.Csv;
import org.h2.util.IOUtils;

import com.firesrc.bean.ProcessorBean;
import com.firesrc.db.DBManager;

public class SqlTsvTool {
  /**
   * 在生成字符串的过程中需要生成临时文件,效率低,不推荐使用
   * @param sql
   * @param filePath 产生的cvs文件
   * @return 根据sql产生的csv格式的文本
   */
  public static String getStringGeneralFile(String sql, String filePath) {
    try {
      Connection conn = DBManager.getPublicConnectionPool().getConnection();
      Statement stat = conn.createStatement();
      stat.execute("call csvwrite('" + filePath + "'," + "'" + sql + " '" + ", 'UTF-8',' ')");
      conn.close();
      String x = IOUtils.readStringAndClose(IOUtils.getReader(FileUtils.newInputStream(filePath)), -1);
      DBManager.getPublicConnectionPool().closeConnection(conn);
      return x;
    } catch (Exception e) {
      System.err.println("SqlTsvTool出错");
    }
    return "";
  }

  /**
   * 直接从sql语句中返回想要的cvs格式的文本数据,不需要生成临时文件,速度较快,推荐使用
   * @param sql 要产生数据的的sql
   * @return
   */
  public static String getStringNoFile(String sql) {
    StringWriter writer = new StringWriter();
    try {
      Connection conn = DBManager.getPublicConnectionPool().getConnection();
      Statement stat = conn.createStatement();
      ResultSet rs = stat.executeQuery(sql);
      Csv csv = getCurrentCsv();
      csv.write(writer, rs);
      rs.close();
      rs = null;
      DBManager.getPublicConnectionPool().closeConnection(conn);
    } catch (Exception e) {
      e.printStackTrace();
    }
    return writer.toString();
  }

  /**
   * 要生成临时文件,不推荐使用
   * @deprecated
   * @param sql
   * @return
   */
  public static String getString(String sql) {
    String createTempFile = "test.jpg";
    try {
      createTempFile = FileUtils.createTempFile("test", "tmp", true, true);
    } catch (IOException e) {
      e.printStackTrace();
    }
    return getStringGeneralFile(sql, createTempFile);
  }

  public static ResultSet getRstFromProcessorBean(ProcessorBean processorBean) {
    String data = processorBean.getData();
    return getRstFromCsvString(data);
  }

  public static ResultSet getRstFromCsvString(String data) {
    ResultSet rst = null;
    try {
      rst = getCurrentCsv().read(new StringReader(data), null);
    } catch (Exception e) {
      e.printStackTrace();
    }
    return rst;
  }

  /**
   * 一个工厂方法,设定了默认生成csv文件的换行符,字段分隔符,还有null字段的处理
   * @return
   */
  public static Csv getCurrentCsv() {
    Csv csv = new Csv();
    csv.setFieldDelimiter((char) 0);
    csv.setLineSeparator(";");
    csv.setLineSeparator("\r\n");
    csv.setNullString("null");
    return csv;
  }

}
